EBMS Main Documentation
Click here to see this page in full context

EBMS Excel Formula Overview

The EBMS Excel Add-in interface allows the user to access EBMS data directly from within MS Excel. EBMS Information, values, and totals such as general ledger financial information are updated live within a spreadsheet.  Sales, inventory, labor and other details from EBMS can generate graphs or create projections using the powerful tools of the MS Excel spreadsheet.  These flexible formula tools allow the user to access most information including text, values, and settings within the EBMS data set.  The EBMS Excel Add-in can be used to generate summary reports, graphs and charts, and other dashboard controls.

Scenario: A business manager for a large industrial equipment company that sales, services, and rents equipment needs a simple dashboard with some key business controls such as sales levels, margins, labor matrixes, cash flow, and important financial indicators.   Simple and flexible visual aids such as graphics, charts, and color indicators are valuable to show a host of key controls quickly and simply.    The manager can add or remove information at will to meet the goals and targets of the company.    Adding a MS Excel spreadsheet dashboard on his computer desktop that is connected to live EBMS data meets this manager's needs.

Scenario: The business consultant for a home improvement center wishes to create a visual aid for the owner as well as individual store managers.    He has created a Power Point presentation with an embedded MS Excel charts that  owners and managers review regularly.    This presentation gives the key managers of the business a means of regularly review business goals in context with live updated financial information from EBMS.  This presentation may be edited by the consultant to reflect current goals or targets within the company.

Scenario: A nonprofit organization requires some unique financial statements to summarize individual funds or projects.   The MS Excel document can be formatted to create a presentation for the board or managers.  This tool allows the user to use a familiar tool such as MS Excel to create the report needed rather than creating a custom report with EBMS using SAP Crystal Reports.    Financial amounts, department summaries, and key indicators directly from EBMS can give managers and controls the info they need in a simple concise manner.  

Excel Add-in ERP Support Training

Consider the following items before continuing with this feature:

  1. The Excel Add-in may negatively effect the performance of EBMS.   Review Speed Issues with Excel Add-in for tips on reducing this issue.

  2. The Excel Add-in  feature requires that a 32 bit version of MS Excel or Office 365 is installed. Note that any 64 bit version of MS Excel are not compatible with EBMS functions.  

  3. The feature is a 3rd party add-in solution that may require some IT technical expertise.    Review Excel Add-in Troubleshooting for troubleshooting tips.  

Getting Started

This feature must be installed from the EBMS installation CD after MS Excel is installed on the local computer. Review the Installing the EBMS Excel Add-in section for more details on installing this option.

Download the sample Excel files located in https://info.eaglebusinesssoftware.com/EBMS/Modules/MSExcel.  Unzip these files and select the Excel-Add-in-Validation-Sample.xlsx file.  

Open the this MS Excel file as shown below.   The EBMS Log in, Calculate function, and Errors log is found on the Add-Ins ribbon as shown below.  Review Add-in tab does not show in Excel for tips if the EBMS interface with Excel does not operate properly.    

Replace the customer ID text with a valid customer ID in cell A7.  The Excel Add-in should insert the customer's last name into the spreadsheet cell.  See additional instructions below on the EBMSLookup function.

Click on the Login icon to log into a specific data set.  The login process is launched if a spreadsheet is opened that contains EBMS Excel Add-in functions.   Select the correct data from the login dialog.   Review Initial Installation and Upgrades > Login Procedures for more EBMS login instructions.

Select the Calculate icon within the Excel Add-in tab to update the EBMS results of the Excel spreadsheet.   

Select the Excel Add-in > Errors icon to view the error log.   This error log is a valuable tool to diagnose EBMS function errors.   Review Troubleshooting > Add-in Error Log for more details.

Function Examples

Some of the common functions used to access EBMS are listed below: Review the EBMS Formula Syntax section for a complete list of functions and formula syntax.

  1. General EBMS lookup function:  EBMSLookup( data type, primary query) is used to access information on a primary record such as a customer, vendor, or inventory item.

    1. EBMSLookup( "Customer.L_name", "DOEJOH"):  This function will return the Last Name (main name) of a customer with an ID of DOEJOH.  The user can enter a data type alias customer instead of the file name, as shown in example B. Review the data type alias details in the EBMS Formula Data Types section.

    2. EBMSLookup( "ARCUST.L_name", "DOEJOH"):  This example replaces the data type alias customer from example A, with the actual file name. The primary query will use the natural key such as the customer ID if no query function is used. The more flexible query is described in example C.

    3. EBMSLookup( "ARCUST.L_name", EbmsQuery("ID='DOEJOH'")): This syntax will return the same value as the previous two examples. The file name ARCUST and the field name L_Name are separated by a period.  They can both be identified by completing the following steps:

      1. Open a customer record and click on the General tab.

      2. Right click on the customer's Last Name, hold the <Shift> and <Ctrl> buttons on the keyboard while selecting the What's This? option on the context menu. The following dialog will appear:

      3. The File name and the Field name can be identified by this dialog as shown above.  These fields names can be used to identify the data that is being retrieved or can be used to query the desired records.  
        Note that some entry fields are virtual and not stored in the database.   These virtual fields cannot be used to query data.  Review Technical > EBMS Tables and Fields for more details.

    4. EBMSLookup( "ARINV.inv_date", "123456" ) to look up invoice 123456 and return the invoice date. The primary query will use the natural key (invoice number for sales invoice table) if no query function is used. The file name ARINV and the field name Inv_date are separated by a period.

    5. EBMSLookup( "ARINV.inv_date", C3 ): This example is similar to the previous example but the invoice number is located within the Excel cell - C3.
      Review the EBMS Formula Syntax section for more formula syntax details.

  2. Return an account balance:  EBMSHistoryBalance( data type, account, year, month ) is used to return balances such as a customer's receivable, vendor's payables, an inventory value, and a financial account balance.  See D15 in the sample spreadsheet for a checking account balance example.

    1. Customer DOEJOH's balance at the end of August = EBMSHistoryBalance( "Customer", "DOEJOH", 2020, 8)

    2. Product item DIRSHO's value at the end of the year = EbmsHistoryBalance("Inventory", "DIRSHO", 2020):  If the month value is not entered the system will use the last month of the 2020 fiscal year.

    3. Checking account 01100-000's current balance = EbmsHistoryBalance("GL", "01100-000"):  This function will return the most recent checking account balance as shown in the Excel-Add-in-Validation-Sample.xlsx file.

    4. Employee JEFMIK's pay total = EBMSHistoryBalance("Employee", "JEFMIK", 2020) for the calendar year 2020.

    5. Budget total at the end of the 3rd quarter (September) = EBMSHistoryBalance( "GLBGT", "51000-010", 2020, 9)

  3. Return total value for one or more records: EBMSSumIf( data type, primary query ) is used to access the sum total of the field identified as shown in the examples below:

    1. EBMSSumIf( "Inventory.Cost", "DIRSHO"):  This function will return the inventory cost of the inventory item with an ID of DIRSHO. The user can enter a data type alias inventory instead of the file name. Review the data type alias details in the EBMS Formula Data Types section.

    2. EBMSSumIf( "INVENTRY.Cost", EbmsRange("DA", "DZZZ")):  This syntax will return the sum cost of all inventory items with an ID between DA and DZZZ.

    3. EBMSSumIf( "INVENTRY.Cost", EbmsStartsWith("D")):  This syntax will return the sum cost of all the inventory items that start with D.

  4. Return value from an account history tab: EBMSHistoryTotal( data type, primary query, begin year, begin month, end year, end month ) is used to access the sum total of values within the history tabs of EBMS records.  See D19 in the sample spreadsheet for a financial account balance example.

    1. EBMSHistoryTotal( "GL.Difference", "50000-000", 2015, 1, 2015,13): This function will return the difference between the debit and credit calculation of the general ledger account: 50000-000. The user should use a data type alias that contains a period.

    2. EBMSHistoryTotal( "GL.Difference", EbmsRange("50000-000", "59999-999"):  This syntax will return the sum cost of all general ledger accounts between 50000-000 and 59999-999 within the entire current fiscal year.

    3. EBMSHistoryTotal("GL.Budget", EBMSRange("50000-000","59999-999"), EBMSFiscalYear(0),4, EBMSFiscalYear(0), 6):  This syntax will return the budget amount all general ledger accounts between 50000-000 and 59999-999 for the 2nd quarter of the current fiscal year.

    4. EBMSHistoryTotal("ProfitCenter.Budget", EbmsProfitCenterQuery("PCA",EBMSRange("50000-000","59999-999")), EBMSFiscalYear(0),4, EBMSFiscalYear(0), 6):  This syntax will return the budget amount for profit center PCA within accounts 50000-000 and 59999-999 for the 2nd quarter of the current fiscal year. Use the EBMSFiscalYear() function if the fiscal year for the current company does not equal a calendar year.   Note that this function is not needed if the company's fiscal year is the same as the calendar year.  The EBMSFiscalYear() function must be used as a nested function within another function that has a year parameter.   Example using the EBMSHistoryTotal() function:

  5. Return the value of one or more detail records: EBMSDetailLookup( data type, primary query, detail query) is used to access information within detail records such as invoice detail, timecard detail, inventory  price records, and other detail records:  

    1. EBMSDetailLookup( arinv.detail[arinvdet].quan, "1100", EbmsQuery("inven='DUCTAP") will return the quantity of the detail line of invoice 1100 with the inventory item of DUCTAP. The file name ARINV has the detail suffix with the file name arinvdet in brackets []. The field name quan is suffixed by detail.

  6. Return total value for one or more detail records:  EBMSDetailSumIf( data type, primary query, detail query ) is used to access the sum total of the detail lines. Use the query function to identify a range of records instead of a single field as shown in example below:

    1. EBMSDetailSumif("arinv.detail[arinvdet].quan",EbmsQuery("Cust_ID='DOEJOH'"),EbmsQuery("inven='DIRSHO'")) will return the quantity total of all detail lines that contain inventory item DIRSHO for all invoices for customer DOEJOH.

Review the EBMS Formula Syntax section for more formula syntax details.

Review the data type alias options within the EBMS Formula Data Types section.

Review Troubleshooting > Add-in Error Log for error log instructions to identify function or data type syntax errors.